--exec GetAllTender_91220 'tender','','','','',1,1,10,4  
CREATE  proc [dbo].[GetAllTender_91220]  
@Description nvarchar(max) = null,  
@Ref_No varchar(200) = null,  
@Tender_Date DATE = null,  
@Issued_By nvarchar(250) = null,  
@Last_Date DATE = null,  
@callType int=0,  
 @PageIndex INT = 1,    
 @PageSize INT = 10,    
 @RecordCount INT OUTPUT    
as  
begin  
 if(@callType = 1)  
  begin  
   select ROW_NUMBER() OVER    
      (    
        ORDER BY CAST(Last_Date AS DATETIME) desc    
          )AS RowNumber,Description,Ref_No,CONVERT(VARCHAR(10), CAST(Tender_Date AS DATETIME), 105) AS Tender_Date,Issued_By,CONVERT(VARCHAR(10), CAST(Last_Date AS DATETIME), 105) AS Last_Date,Doc_Path,CAST(Response as varchar(max)) as Response INTO #Resu
ltssurch  from [dbo].[Tenders]    
   where (Description LIKE '%' + @Description + '%') or (Ref_No LIKE '%' + @Ref_No + '%') or   
   (convert(date,Tender_Date,105) BETWEEN convert(date,@Tender_Date,105) AND convert(date,GETDATE(),105)) or (Issued_By LIKE '%' + @Issued_By + '%') or  
   (convert(date,Last_Date,105) BETWEEN convert(date,@Last_Date,105) AND convert(date,GETDATE(),105))  
   --ORDER BY CAST(Last_Date AS DATETIME) desc   
    SELECT * FROM #Resultssurch   
    WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1    
     SELECT @RecordCount = COUNT(*) FROM #Resultssurch    
    DROP TABLE #Resultssurch   
  end  
 if(@callType = 2)  
  begin  
   select ROW_NUMBER() OVER    
      (    
        ORDER BY CAST(Last_Date AS DATETIME) desc    
          )AS RowNumber, Description,Ref_No,CONVERT(VARCHAR(10), CAST(Tender_Date AS DATETIME), 111) AS Tender_Date,Issued_By,CONVERT(VARCHAR(10), CAST(Last_Date AS DATETIME), 111) AS Last_Date,Doc_Path,CAST(Response as varchar(max)) as Response  INTO #Re
sultstender from [dbo].[Tenders]    
  -- where (convert(date,Tender_Date,111) BETWEEN convert(date,cast('2017-03-29' as date),111) AND --convert(date,GETDATE(),111))  
  -- where (convert(date,Tender_Date,105) BETWEEN convert(date,cast('2017-03-29' as date),105) AND convert(date,cast('2025-03-29' as date),105))  
   where (convert(date,Last_Date,111)>= convert(date,GETDATE()-7,111))  
   --ORDER BY CAST(Last_Date AS DATETIME) desc  
   SELECT * FROM #Resultstender    
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1    
     SELECT @RecordCount = COUNT(*) FROM #Resultstender    
    DROP TABLE #Resultstender   
  end  
  
  if(@callType = 3)  
  begin  
   declare @mindate date;  
   select @mindate =  min(convert(date,Tender_Date,105))  from [Tenders];  
  
   select ROW_NUMBER() OVER    
      (    
        ORDER BY CAST(Last_Date AS DATETIME) desc    
          )AS RowNumber,  Description,Ref_No,CONVERT(VARCHAR(10), CAST(Tender_Date AS DATETIME), 105) AS Tender_Date,Issued_By,CONVERT(VARCHAR(10), CAST(Last_Date AS DATETIME), 105) AS Last_Date,Doc_Path,CAST(Response as varchar(max)) as Response INTO #Re
sults  from [dbo].[Tenders]    
   ---where (convert(date,Tender_Date,105) BETWEEN @mindate AND convert(date,cast('2017-03-28' as date),105))  
    where (convert(date,Last_Date,111) BETWEEN (convert(date,cast('2017-03-28' as date),105)) AND convert(date,GETDATE()-7,111))  
   --ORDER BY CAST(Last_Date AS DATETIME) desc  
   --where (convert(date,Last_Date,111)<= convert(date,GETDATE()-7,111))  
   --ORDER BY CAST(Last_Date AS DATETIME) desc   
    SELECT * FROM #Results    
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1    
        
      SELECT @RecordCount = COUNT(*) FROM #Results    
          
      DROP TABLE #Results    
  end  
  
end